package controle.aluno;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;
import java.text.SimpleDateFormat;

import javax.swing.JOptionPane;

import controle.conexao;



import modelagem.Aluno;
import modelagem.Sexo;

public class alunoDAO {

	public long inserir(Aluno aluno) {
		long id = -1;
		
		DateFormat format = new SimpleDateFormat("dd/MM/yyyy", Locale.US);
		format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));

		java.util.Date date = null;
		try {
			date = format.parse(aluno.getData_nasc());
		} catch (ParseException e1) {
			e1.printStackTrace();
		}

		java.sql.Date sqlDate = new java.sql.Date(date.getTime());
		
				
		String sql = "insert into cont_academico."
				+ "aluno(nome, matricula, telefone, data_nasc," 
				+"sexo, rg, cpf, email, endereco, observacoes)" 
				+ "values(?,?,?,?,?,?,?,?,?,?)";
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql,
					PreparedStatement.RETURN_GENERATED_KEYS);
			statement.setString(1, aluno.getNome());
			statement.setString(2, aluno.getMatricula());
			statement.setString(3, aluno.getTelefone());
			statement.setDate(4, sqlDate);
			statement.setString(5, aluno.getS().getSexo());
			statement.setString(6, aluno.getRg());
			statement.setString(7, aluno.getCpf());
			statement.setString(8, aluno.getEmail());
			statement.setString(9, aluno.getEndereco());
			statement.setString(10, aluno.getObservacao());
			statement.executeUpdate();
			ResultSet set = statement.getGeneratedKeys();
			while (set.next()) {
				id = set.getLong("id");
			}
			JOptionPane.showMessageDialog(null, "Salvo com sucesso!");
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return id;
	}
	
	
	
	public List<Aluno> buscar(String nome, String matricula){
		String sql;
		Aluno aluno;
		List<Aluno> lista = new ArrayList<Aluno>();
		int count = 0;
		if(matricula.equals("") == true)
			sql = "select * from cont_academico."
				+ "aluno where nome like ? order by nome;";
		else{
			sql = "select * from cont_academico."
					+ "aluno where matricula like ? order by nome;";
			count = 1;
		}
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, "%"+nome+"%");
			if(count == 1)
				statement.setString(1, "%"+matricula+"%");
			
			statement.execute();
			ResultSet set = statement.getResultSet();
			
			while (set.next()) {
				aluno = new Aluno();
				Sexo s;
			    SimpleDateFormat formatador = new SimpleDateFormat("dd/MM/yyyy");  
			    String dataFormatada = formatador.format(set.getDate("data_nasc"));  
				if(set.getString("sexo").equals("M"))
					s = Sexo.M;
				else
					s = Sexo.F;
				
				aluno.setId(set.getInt("id"));
				aluno.setNome(set.getString("nome"));
				aluno.setMatricula(set.getString("matricula"));
				aluno.setCpf(set.getString("cpf"));
				aluno.setRg(set.getString("rg"));
				aluno.setS(s);
				aluno.setEmail(set.getString("email"));
				aluno.setTelefone(set.getString("telefone"));
				aluno.setEndereco(set.getString("endereco"));
				aluno.setData_nasc(dataFormatada);
				aluno.setObservacao(set.getString("observacoes"));
				lista.add(aluno);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return lista;
	}
	
	
	
	public void atualizar(Aluno aluno){
		DateFormat format = new SimpleDateFormat("dd/MM/yyyy", Locale.US);
		format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));

		java.util.Date date = null;
		try {
			date = format.parse(aluno.getData_nasc());
		} catch (ParseException e1) {
			e1.printStackTrace();
		}

		java.sql.Date sqlDate = new java.sql.Date(date.getTime());
		
				
		String sql = "update cont_academico."
				+ "aluno set (nome, matricula, telefone, data_nasc," 
				+"sexo, rg, cpf, email, endereco, observacoes)" 
				+ " = (?,?,?,?,?,?,?,?,?,?) where id = ?";
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, aluno.getNome());
			statement.setString(2, aluno.getMatricula());
			statement.setString(3, aluno.getTelefone());
			statement.setDate(4, sqlDate);
			statement.setString(5, aluno.getS().getSexo());
			statement.setString(6, aluno.getRg());
			statement.setString(7, aluno.getCpf());
			statement.setString(8, aluno.getEmail());
			statement.setString(9, aluno.getEndereco());
			statement.setString(10, aluno.getObservacao());
			statement.setInt(11, aluno.getId());
			statement.execute();
			
			JOptionPane.showMessageDialog(null, "Atualizado com sucesso!");
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	}

}


